using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.OracleClient;
using System.Collections.Generic;
using Pms.Utils;
using Biblioteca.Utils;
using Biblioteca.DataBase;
/*
 * Autor      : IT-Developer1
 * Data       : 02/04/2010 11:03:48
 * Descricao  : 
 * Alteracoes : 
 * 
 */

namespace SERIX
{
   public class CLotacoesTecnicos
   {
       private string IDT_LOTACAO ;
       private string IDT_TEC ;
       private string IDT_MUN ;
       private string DAT_INI ;
       private string STA_LOTACAO ;
       private string DAT_FIM ;
       private string NOME_MUN;

	   /// <sumary>
       /// Construtor da classe lotacoes_tecnicos
       /// <sumary>
       public CLotacoesTecnicos()
       {
   
       }

	   /// <sumary>
       /// Construtor da classe Lotacoes_tecnicos
       /// <sumary>
       public CLotacoesTecnicos( string IDT_LOTACAO, string IDT_TEC, string IDT_MUN, string DAT_INI, string STA_LOTACAO, string DAT_FIM )
       {
          this.IDT_LOTACAO = IDT_LOTACAO;
          this.IDT_TEC = IDT_TEC;
          this.IDT_MUN = IDT_MUN;
          this.DAT_INI = DAT_INI;
          this.STA_LOTACAO = STA_LOTACAO;
          this.DAT_FIM = DAT_FIM;
       }

       public CLotacoesTecnicos(string IDT_LOTACAO, string IDT_TEC, string IDT_MUN, string DAT_INI, string STA_LOTACAO, string DAT_FIM, string NOME_MUN)
       {
           this.IDT_LOTACAO = IDT_LOTACAO;
           this.IDT_TEC = IDT_TEC;
           this.IDT_MUN = IDT_MUN;
           this.DAT_INI = DAT_INI;
           this.STA_LOTACAO = STA_LOTACAO;
           this.DAT_FIM = DAT_FIM;
           this.NOME_MUN = NOME_MUN;
       }

	   #region Propriedades
     	/// <sumary>
        /// Identificador da lotacao
        /// <sumary>
        public string Idt_lotacao 
		{
		   get
		   {
		       return this.IDT_LOTACAO;
		   }
		   set
		   {
		       this.IDT_LOTACAO = value;
		   }
		}
     	/// <sumary>
        /// Identificador do tecnico
        /// <sumary>
        public string Idt_tec 
		{
		   get
		   {
		       return this.IDT_TEC;
		   }
		   set
		   {
		       this.IDT_TEC = value;
		   }
		}
     	/// <sumary>
        /// Identificador do escritorio local
        /// <sumary>
        public string Idt_mun 
		{
		   get
		   {
		       return this.IDT_MUN;
		   }
		   set
		   {
		       this.IDT_MUN = value;
		   }
		}
     	/// <sumary>
        /// Data de inicio da lotacao no escritorio local
        /// <sumary>
        public string Dat_ini 
		{
		   get
		   {
		       return this.DAT_INI;
		   }
		   set
		   {
		       this.DAT_INI = value;
		   }
		}
     	/// <sumary>
        /// Situacao da lotacao: A - Ativo, I - Inativo
        /// <sumary>
        public string Sta_lotacao 
		{
		   get
		   {
		       return this.STA_LOTACAO;
		   }
		   set
		   {
		       this.STA_LOTACAO = value;
		   }
		}
     	/// <sumary>
        /// Data de fim de lotacao do tecnico no escritorio local
        /// <sumary>
        public string Dat_fim 
		{
		   get
		   {
		       return this.DAT_FIM;
		   }
		   set
		   {
		       this.DAT_FIM = value;
		   }
		}

        public string Nome_mun
		{
		   get
		   {
		       return this.NOME_MUN;
		   }
		   set
		   {
		       this.NOME_MUN = value;
		   }
		}
       #endregion Propriedades
	
	    /// <summary>
        /// Inserindo um novo Lotacoes_tecnicos.
        /// </summary>
        public void Inserir(string Login)
        {
		    OracleCommand command = new OracleCommand();
            OracleTransaction trans;

            string SQL = @"insert into Serix.SRX_LOTACOES_TECNICOS
			                  ( IDT_LOTACAO, IDT_TEC, IDT_MUN, DAT_INI, STA_LOTACAO, DAT_FIM )
						   values
						      ( :IDT_LOTACAO, :IDT_TEC, :IDT_MUN, :DAT_INI, :STA_LOTACAO, :DAT_FIM )";
     		
			try
			{	
				command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, Login, BiblioPMS.Demo);
                trans = command.Connection.BeginTransaction();
                command.Transaction = trans;
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;
				
				command.Parameters.Add(new OracleParameter("IDT_LOTACAO",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("IDT_TEC",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("IDT_MUN",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("DAT_INI",OracleType.DateTime)); 
				command.Parameters.Add(new OracleParameter("STA_LOTACAO",OracleType.Char)); 
				command.Parameters.Add(new OracleParameter("DAT_FIM",OracleType.DateTime)); 
				
                command.Parameters["IDT_LOTACAO"].Value = this.IDT_LOTACAO;
                command.Parameters["IDT_TEC"].Value = this.IDT_TEC;
                command.Parameters["IDT_MUN"].Value = this.IDT_MUN;
                command.Parameters["DAT_INI"].Value = this.DAT_INI;
                command.Parameters["STA_LOTACAO"].Value = this.STA_LOTACAO;
                if (String.IsNullOrEmpty(this.DAT_FIM))
                {
                    command.Parameters["DAT_FIM"].Value = DBNull.Value;
                }
                else
                {
                    command.Parameters["DAT_FIM"].Value = this.DAT_FIM;
                }  
				command.ExecuteNonQuery();
                command.Parameters.Clear();
                command.Transaction.Commit();
            }
            catch (Exception e)
            {
                command.Transaction.Rollback();

                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
		}
		
	    /// <summary>
        /// Alterando um Lotacoes_tecnicos.
        /// </summary>
        public void Alterar(string Login)
        {
		    OracleCommand command = new OracleCommand();
            OracleTransaction trans;

            string SQL = @"update Serix.SRX_LOTACOES_TECNICOS set 
			                   IDT_TEC = :IDT_TEC, IDT_MUN = :IDT_MUN, DAT_INI = :DAT_INI, STA_LOTACAO = :STA_LOTACAO, DAT_FIM = :DAT_FIM
						   where
						       IDT_LOTACAO = :IDT_LOTACAO ";
     		
			try
			{	
				command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, Login, BiblioPMS.Demo);
                trans = command.Connection.BeginTransaction();
                command.Transaction = trans;
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;
				
				command.Parameters.Add(new OracleParameter("IDT_LOTACAO",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("IDT_TEC",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("IDT_MUN",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("DAT_INI",OracleType.DateTime)); 
				command.Parameters.Add(new OracleParameter("STA_LOTACAO",OracleType.Char)); 
				command.Parameters.Add(new OracleParameter("DAT_FIM",OracleType.DateTime)); 
				
                command.Parameters["IDT_LOTACAO"].Value = this.IDT_LOTACAO;
                command.Parameters["IDT_TEC"].Value = this.IDT_TEC;
                command.Parameters["IDT_MUN"].Value = this.IDT_MUN;
                command.Parameters["DAT_INI"].Value = this.DAT_INI;
                command.Parameters["STA_LOTACAO"].Value = this.STA_LOTACAO;
                if (String.IsNullOrEmpty(this.DAT_FIM))
                {
                    command.Parameters["DAT_FIM"].Value = DBNull.Value;
                }
                else
                {
                    command.Parameters["DAT_FIM"].Value = this.DAT_FIM;
                }  
				command.ExecuteNonQuery();
                command.Parameters.Clear();
                command.Transaction.Commit();
            }
            catch (Exception e)
            {
                command.Transaction.Rollback();

                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
		}
		
	     /// <summary>
        /// Exclui um  Lotacoes_tecnicos.
        /// </summary>
        public void Excluir(string Login)
        {
            Tabela tab = new Tabela("SRX_LOTACOES_TECNICOS", BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, Login,BiblioPMS.Demo));
            tab.adicionarFiltro(new Coluna("IDT_LOTACAO", DbType.Int32, this.IDT_LOTACAO));
            tab.delete();
        }
		
		static public DataTable ObterTodos()
        {
            string login = "serix";
            OracleCommand command = new OracleCommand();

            string SQL = @"select * from Serix.SRX_LOTACOES_TECNICOS order by IDT_LOTACAO ";

            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;

                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection)); 
                return tbl; 
                
            }
            catch (Exception e)
            {
 
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }

        }
		
		static public CLotacoesTecnicos PesquisaporCodigo(string idt_lotacao, string login)
        {
            CLotacoesTecnicos obj;
            string SQL = @"SELECT IDT_TEC, IDT_LOTACAO, LT.IDT_MUN, TO_CHAR(DAT_INI, 'DD/MM/YYYY') DAT_INI, TO_CHAR(DAT_FIM, 'DD/MM/YYYY') DAT_FIM, STA_LOTACAO, M.NOME_MUN
                            FROM Serix.SRX_LOTACOES_TECNICOS LT JOIN Serix.SRX_MUNICIPIOS M ON (LT.IDT_MUN = M.IDT_MUN)
                            WHERE IDT_LOTACAO = :IDT_LOTACAO";
            Query qy = null;
            try
            {
                qy = new Query(BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, BiblioPMS.Demo), SQL);
				qy.AdicionarParametro(":IDT_LOTACAO", DbType.String, idt_lotacao);
               
				qy.Executar();
                obj = new CLotacoesTecnicos(qy.ObterCampo("IDT_LOTACAO"), qy.ObterCampo("IDT_TEC"), qy.ObterCampo("IDT_MUN"), qy.ObterCampo("DAT_INI"), qy.ObterCampo("STA_LOTACAO"), qy.ObterCampo("DAT_FIM"), qy.ObterCampo("NOME_MUN"));
            }                                  
            catch
            {
                if (qy.EstadoConexao() == ConnectionState.Open)
                {
                    qy.FecharConexao();
                }
                return new CLotacoesTecnicos();
            }
			finally
            {
                qy.FecharConexao();
            }
            return obj;
        }
		
		static public int ObterNovoIdt(string login)
        {
            string SQL = @"SELECT PKG_BIBSERIX.OBTER_IDT_LOTACAO() IDT From dual ";
            Query qy = null;
            try
            {
                qy = new Query(BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, BiblioPMS.Demo), SQL);
				qy.Executar();
				return Convert.ToInt32(qy.ObterCampo("IDT"));
            }
            catch
            {
                if (qy.EstadoConexao() == ConnectionState.Open)
                {
                    qy.FecharConexao();
                }
                return-1;
            }
			finally
            {
                qy.FecharConexao();
            }
        }
		
		static public DataTable ObterPorIDT_TEC(string idt_tec)
        {
            string login = "serix";
            OracleCommand command = new OracleCommand();

            string SQL = @"select * from Serix.SRX_LOTACOES_TECNICOS  where IDT_TEC = :IDT_TEC order by IDT_LOTACAO ";

            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;
                command.Parameters.Add(new OracleParameter("IDT_TEC", OracleType.Number));
                command.Parameters["IDT_TEC"].Value = idt_tec;
                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                return tbl;
            }
            catch (Exception e)
            {
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }

        }
		static public DataTable ObterPorIDT_MUN(string idt_mun)
        {
            string login = "serix";
            OracleCommand command = new OracleCommand();

            string SQL = @"select * from Serix.SRX_LOTACOES_TECNICOS  where IDT_MUN = :IDT_MUN order by IDT_LOTACAO ";

            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;
                command.Parameters.Add(new OracleParameter("IDT_MUN", OracleType.Number));
                command.Parameters["IDT_MUN"].Value = idt_mun;
                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                return tbl;
            }
            catch (Exception e)
            {
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }

        }
    }
}	

